package com.example.gymmanagebackend.mapper;

import com.example.gymmanagebackend.pojo.Members;
import org.apache.ibatis.annotations.*;
import org.springframework.data.domain.Pageable;

import java.util.List;

@Mapper
public interface MembersMapper {

    @Options(useGeneratedKeys = true, keyProperty = "member_id", keyColumn = "member_id")
    @Insert("INSERT INTO Members (name, gender, birthday, phone, register_date, card_type, expiry_date) VALUES (#{name}, #{gender}, #{birthday}, #{phone}, #{registerDate}, #{cardType}, #{expiryDate})")
    int addMember(Members member);

    @Delete("DELETE FROM Members WHERE name= #{name}")
    int deleteMemberByName(String name);

    @Update("UPDATE Members SET name = #{name}, gender = #{gender}, birthday = #{birthday}, phone = #{phone}, register_date = #{registerDate}, card_type = #{cardType}, expiry_date = #{expiryDate} WHERE phone = #{phone}")
    int updateMember(Members member);

    @Select("SELECT * FROM Members")
    List<Members> getAllMembers();

    @Select("SELECT COUNT(*) FROM Members")
    int count();

    // 添加分页查询方法
    @Select("SELECT * FROM Members")
    @Results({
            @Result(property = "member_id", column = "member_id"),
            @Result(property = "name", column = "name"),
            @Result(property = "gender", column = "gender"),
            @Result(property = "birthday", column = "birthday"),
            @Result(property = "phone", column = "phone"),
            @Result(property = "registerDate", column = "register_date"),
            @Result(property = "cardType", column = "card_type"),
            @Result(property = "expiryDate", column = "expiry_date")
    })
    List<Members> findAll(Pageable pageable);

    @Select("SELECT * FROM Members WHERE member_id = #{member_id}")
    Members findMemberById(Integer id);

    @Select("SELECT * FROM Members WHERE phone = #{phone}")
    Members findMemberByPhone(String phone);

    @Select("SELECT * FROM Members WHERE name = #{name}")
    Members findMemberByName(String name);

    @Select("SELECT phone FROM Members WHERE name = #{name}")
    String findPhoneByName(@Param("name") String name);

    @Select("SELECT YEAR(CURDATE()) - YEAR(birthday) AS age, COUNT(*) AS count FROM Members GROUP BY YEAR(CURDATE()) - YEAR(birthday)")
    List<Members> getAgeDistribution();

    @Select("SELECT gender, COUNT(*) AS count FROM Members GROUP BY gender")
    List<Members> getGenderDistribution();

    @Select("SELECT DATE_FORMAT(register_date, '%Y-%m') AS month, COUNT(*) AS count FROM Members GROUP BY DATE_FORMAT(register_date, '%Y-%m') ORDER BY DATE_FORMAT(register_date, '%Y-%m')")
    List<Members> getMemberGrowthTrend();
    @Select("SELECT card_type AS cardType, COUNT(*) AS count FROM Members GROUP BY card_type")
    List<Members> getCardPurchaseStatistics();
    @Select("SELECT * FROM Members WHERE name LIKE CONCAT('%', #{search}, '%') OR phone LIKE CONCAT('%', #{search}, '%') LIMIT #{offset}, #{limit}")
    @Results({
            @Result(property = "member_id", column = "member_id"),
            @Result(property = "name", column = "name"),
            @Result(property = "gender", column = "gender"),
            @Result(property = "birthday", column = "birthday"),
            @Result(property = "phone", column = "phone"),
            @Result(property = "registerDate", column = "register_date"),
            @Result(property = "cardType", column = "card_type"),
            @Result(property = "expiryDate", column = "expiry_date")
    })
    List<Members> findBySearch(@Param("search") String search, @Param("offset") int offset, @Param("limit") int limit);

    @Select("SELECT COUNT(*) FROM Members WHERE name LIKE CONCAT('%', #{search}, '%') OR phone LIKE CONCAT('%', #{search}, '%')")
    int countBySearch(@Param("search") String search);
}